package com.deng.maintenance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.deng.common.SQLHelper;
import com.deng.common.vo.FunctionVo;
import com.deng.maintenance.dao.IVoteDict;
import com.deng.maintenance.vo.JiangChengDictVo;
import com.deng.maintenance.vo.VoteDictVo;

public class VoteDictImpl extends SQLHelper implements IVoteDict {

	
	public int insertData(VoteDictVo vo) {
		try{
			Connection con = getConnect();
			ResultSet rs = queryData(con,"select case when max(id) is not null then max(id)+1 else 1 end as id" +
					" from vote_dict");			
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("id");
			}
			
			String sql = "insert into vote_dict" +
					" (id,name,vote_definition)" +
					" values(?,?,?)";
			con = getConnect();
			con.setAutoCommit(false);
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, maxID);
			pst.setString(2, vo.name);
			pst.setString(3, vo.vote_definition);
			con.commit();
			freeConnection(con);
			return 1;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int deleteData(int id) {
		try{
			Connection conn = getConnect();
			int x = updateData(conn,"delete vote_dict" +
					" where id = "+id);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;			
	}

	public int updateData(VoteDictVo vo) {
		try{
			Connection conn = getConnect();
			int x = updateData(conn,"update vote_dict" +
					"	set name = '"+vo.name+"'," +
					"	vote_definition ='"+vo.vote_definition+"'" +
					"	where id = "+vo.id);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;		
	}

	public List<VoteDictVo> searchAllData() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from vote_dict order by id");
			List<VoteDictVo> rtL = new ArrayList<VoteDictVo>();
			while(rs.next()){
				VoteDictVo vo = new VoteDictVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.vote_definition = rs.getString("vote_definition");
				rtL.add(vo);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public VoteDictVo searchDataById(int id) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from vote_dict where id = "+id);
			if(rs.next()){
				VoteDictVo vo = new VoteDictVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.vote_definition = rs.getString("vote_definition");
				freeConnection(conn);
				return vo;
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<VoteDictVo> insertDataGetAll(VoteDictVo vo) {

		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select case when max(id) is not null then max(id)+1 else 1 end as id " +
					" from vote_dict");			
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("id");
			}
			
			StringBuffer sqlSB = new StringBuffer("insert into vote_dict (id,name,vote_definition,function_detail,weight) " +
					"	values(");
			sqlSB.append(maxID+",");
			sqlSB.append("'"+vo.name+"',");
			if(vo.vote_definition != null){
				sqlSB.append("'"+vo.vote_definition+"',");
			}else{
				sqlSB.append("null,");
			}
			sqlSB.append("'dbo.FUN_VOTE(@organization_id,@stat_time,@start_time,@end_time,"+maxID+")',");
			if(vo.weight != null){
				sqlSB.append(""+vo.weight+")");
			}else{
				sqlSB.append("1)");
			}
			updateData(conn,sqlSB.toString());
			
			
			
			return searchAllData();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	private String createOrUpdateVoteDictFunction(int voteId,Connection conn){
		try{
			String funName = "FUN_VOTE_"+voteId;
			
			String sql = "select * from sysObjects where name = '"+funName+"'";
			StringBuffer sqlF = new StringBuffer();
			ResultSet rsrs = queryData(conn,sql);
			if(rsrs.next()){//存在
				sqlF.append("ALTER ");
			}else{//不存在
				sqlF.append("CREATE ");
			}
			sqlF.append(" FUNCTION "+funName);
			sqlF.append("\r(");
			sqlF.append("\r    @organization_id int,@stat_time datetime,@start_time nvarchar(10),@end_time nvarchar(10)");
			sqlF.append("\r)");
			sqlF.append("\rRETURNS decimal(30,4)  AS");
			sqlF.append("\rBEGIN");
			sqlF.append("\r    declare @frq_code nvarchar(50);");
			sqlF.append("\r    set @frq_code = (select frq_code from vote_vs_organization where vote_id = "+voteId+"  and organization_id = @organization_id);");
			sqlF.append("\r");
			sqlF.append("\r    if @frq_code = '0001'");//年
			sqlF.append("\r    BEGIN");
			sqlF.append("\r        return ");
			sqlF.append("\r        (select sum(score) from vote_value where  vote_id = "+voteId+"  and organization_id = @organization_id and frq_code = @frq_code	and year(stat_time) = year(@stat_time)) ");
			sqlF.append("\r    END");               
			sqlF.append("\r");
			sqlF.append("\r    else if (@frq_code = '0002')");//半年
			sqlF.append("\r    BEGIN");
			sqlF.append("\r        if month(@stat_time)> = 1 and month(@stat_time) <= 6 ");//上半年
			sqlF.append("\r        BEGIN");
			sqlF.append("\r            return ");
			sqlF.append("\r            (select sum(score) from vote_value where  vote_id = "+voteId+" and organization_id = @organization_id and frq_code = @frq_code and year(stat_time) = year(@stat_time) and month(stat_time) = 1) ");
			sqlF.append("\r        END");
			sqlF.append("\r        else"); //下半年
			sqlF.append("\r        BEGIN"); 
			sqlF.append("\r            return ");
			sqlF.append("\r            (select sum(score) from vote_value	where  vote_id = "+voteId+"  and organization_id = @organization_id and frq_code = @frq_code and year(stat_time) = year(@stat_time) and month(stat_time) = 7)");
			sqlF.append("\r        END");
			sqlF.append("\r    END");               
			sqlF.append("\r");
			sqlF.append("\r    else if (@frq_code = '0003')");//季度
			sqlF.append("\r    BEGIN");
			sqlF.append("\r        if month(@stat_time)> = 1 and month(@stat_time) <= 3 ");//一季度
			sqlF.append("\r        BEGIN"); 
			sqlF.append("\r            return ");
			sqlF.append("\r            (select sum(score) from vote_value where  vote_id ="+voteId+"  and organization_id = @organization_id and frq_code = @frq_code and year(stat_time) = year(@stat_time) and month(stat_time) = 1)");
			sqlF.append("\r        END");
			sqlF.append("\r        else if month(@stat_time)> = 4 and month(@stat_time) <= 6 ");//2季度
			sqlF.append("\r        BEGIN"); 
			sqlF.append("\r            return ");
			sqlF.append("\r            (select sum(score) from vote_value where  vote_id = "+voteId+"  and organization_id = @organization_id and frq_code = @frq_code and year(stat_time) = year(@stat_time) and month(stat_time) = 4)");
			sqlF.append("\r        END");
			sqlF.append("\r        else if month(@stat_time)> = 7 and month(@stat_time) <= 9 ");//3季度
			sqlF.append("\r        BEGIN"); 
			sqlF.append("\r            return ");
			sqlF.append("\r            (select sum(score) from vote_value where  vote_id = "+voteId+"  and organization_id = @organization_id and frq_code = @frq_code and year(stat_time) = year(@stat_time) and month(stat_time) = 7)");
			sqlF.append("\r        END");
			sqlF.append("\r        else if month(@stat_time)> = 10 and month(@stat_time) <= 12 ");//4季度
			sqlF.append("\r        BEGIN"); 
			sqlF.append("\r            return ");
			sqlF.append("\r            (select sum(score) from vote_value where  vote_id = "+voteId+"  and organization_id = @organization_id and frq_code = @frq_code and year(stat_time) = year(@stat_time) and month(stat_time) = 10)");
			sqlF.append("\r        END");			
			sqlF.append("\r    END");  
			sqlF.append("\r");
			sqlF.append("\r    else if (@frq_code = '0004')");//月 
			sqlF.append("\r    BEGIN"); 
			sqlF.append("\r        return ");
			sqlF.append("\r        (select sum(score) from vote_value where  vote_id = "+voteId+"  and organization_id = @organization_id and frq_code = @frq_code and year(stat_time) = year(@stat_time) and month(stat_time) = month(@stat_time))");
			sqlF.append("\r    END");
			sqlF.append("\r");
			sqlF.append("\r    return 0;");
			sqlF.append("\rEND");
			updateData(conn,sqlF.toString());
			return "dbo."+funName+"(@organization_id,@stat_time,@start_time,@end_time)";
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<FunctionVo> getAllVoteFunction() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from vote_dict order by name");
			List<FunctionVo> rtL = new ArrayList<FunctionVo>();
			while(rs.next()){
				FunctionVo fv = new FunctionVo();
				fv.function_detail = rs.getString("function_detail");
				fv.function_name = rs.getString("name");
				fv.function_id = rs.getInt("id");
				rtL.add(fv);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public List<FunctionVo> getAllVoteFunction2() {

		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from vote_dict2 order by id");
			List<FunctionVo> rtL = new ArrayList<FunctionVo>();
			while(rs.next()){
				FunctionVo fv = new FunctionVo();
				fv.function_detail = rs.getString("function_detail");
				fv.function_name = rs.getString("name");
				fv.function_id = rs.getInt("id");
				rtL.add(fv);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<VoteDictVo> searchAllData2() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from VOTE_DICT2 order by id");
			List<VoteDictVo> rtL = new ArrayList<VoteDictVo>();
			while(rs.next()){
				VoteDictVo vo = new VoteDictVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.vote_definition = rs.getString("vote_definition");
				rtL.add(vo);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<VoteDictVo> insertDataGetAll2(VoteDictVo vo) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select case when max(id) is not null then max(id)+1 else 1 end as id " +
					" from VOTE_DICT2");			
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("id");
			}
			
			StringBuffer sqlSB = new StringBuffer("insert into VOTE_DICT2 (id,name,vote_definition,function_detail) " +
					"	values(");
			sqlSB.append(maxID+",");
			sqlSB.append("'"+vo.name+"',");
			if(vo.vote_definition != null){
				sqlSB.append("'"+vo.vote_definition+"',");
			}else{
				sqlSB.append("null,");
			}
			sqlSB.append("'dbo.FUN_VOTE2(@dispense_id,@employee_id,@stat_time,@start_time,@end_time,"+maxID+")')");

			updateData(conn,sqlSB.toString());
			
			return searchAllData2();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public List<VoteDictVo> searchAllDataOperate() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from VOTE_DICT_OPERATE order by id");
			List<VoteDictVo> rtL = new ArrayList<VoteDictVo>();
			while(rs.next()){
				VoteDictVo vo = new VoteDictVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.vote_definition = rs.getString("vote_definition");
				rtL.add(vo);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public List<VoteDictVo> insertDataGetAllOperate(VoteDictVo vo) {


		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select case when max(id) is not null then max(id)+1 else 1 end as id " +
					" from vote_dict_operate");			
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("id");
			}
			
			StringBuffer sqlSB = new StringBuffer("insert into vote_dict_operate (id,name,vote_definition,function_detail,weight) " +
					"	values(");
			sqlSB.append(maxID+",");
			sqlSB.append("'"+vo.name+"',");
			if(vo.vote_definition != null){
				sqlSB.append("'"+vo.vote_definition+"',");
			}else{
				sqlSB.append("null,");
			}
			sqlSB.append("'dbo.FUN_VOTE_OPERATE(@operate_id,@stat_time,@start_time,@end_time,"+maxID+")',");
			if(vo.weight != null){
				sqlSB.append(""+vo.weight+")");
			}else{
				sqlSB.append("1)");
			}
			updateData(conn,sqlSB.toString());
			
			
			
			return searchAllDataOperate();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	
	}

	public List<FunctionVo> getAllVoteFunctionOperate() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from VOTE_DICT_OPERATE order by id");
			List<FunctionVo> rtL = new ArrayList<FunctionVo>();
			while(rs.next()){
				FunctionVo fv = new FunctionVo();
				fv.function_detail = rs.getString("function_detail");
				fv.function_name = rs.getString("name");
				fv.function_id = rs.getInt("id");
				rtL.add(fv);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public List<FunctionVo> getAllVoteFunctionAdmin() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from vote_dict_ADMIN order by id");
			List<FunctionVo> rtL = new ArrayList<FunctionVo>();
			while(rs.next()){
				FunctionVo fv = new FunctionVo();
				fv.function_detail = rs.getString("function_detail");
				fv.function_name = rs.getString("name");
				fv.function_id = rs.getInt("id");
				rtL.add(fv);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	
	}

	public List<VoteDictVo> insertDataGetAllAdmin(VoteDictVo vo) {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select case when max(id) is not null then max(id)+1 else 1 end as id " +
					" from vote_dict_admin");			
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("id");
			}
			
			StringBuffer sqlSB = new StringBuffer("insert into vote_dict_admin (id,name,vote_definition,function_detail,weight) " +
					"	values(");
			sqlSB.append(maxID+",");
			sqlSB.append("'"+vo.name+"',");
			if(vo.vote_definition != null){
				sqlSB.append("'"+vo.vote_definition+"',");
			}else{
				sqlSB.append("null,");
			}
			sqlSB.append("'dbo.FUN_VOTE_ADMIN(@admin_id,@stat_time,@start_time,@end_time,"+maxID+")',");
			if(vo.weight != null){
				sqlSB.append(""+vo.weight+")");
			}else{
				sqlSB.append("1)");
			}
			updateData(conn,sqlSB.toString());
			
			
			
			return searchAllDataAdmin();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	
	}

	public List<VoteDictVo> searchAllDataAdmin() {

		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from VOTE_DICT_ADMIN order by id");
			List<VoteDictVo> rtL = new ArrayList<VoteDictVo>();
			while(rs.next()){
				VoteDictVo vo = new VoteDictVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.vote_definition = rs.getString("vote_definition");
				rtL.add(vo);
			}
			freeConnection(conn);
			return rtL;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	
	}

	public List<VoteDictVo> updateVoteDict(VoteDictVo vo, int type) {
		try
		{
			String sql = "";
			List<VoteDictVo> l = null;
			if(type == 1){					
				sql = "update vote_dict set name = '"+vo.name+"' where id ="+vo.id;
			}else if(type == 2){
				sql = "update VOTE_DICT_OPERATE set name = '"+vo.name+"' where id ="+vo.id;
			}else if(type == 3){
				sql = "update VOTE_DICT_ADMIN set name = '"+vo.name+"' where id ="+vo.id;
			}else if(type == 4){
				sql = "update VOTE_DICT2 set name = '"+vo.name+"' where id ="+vo.id;			
			}
			Connection conn = getConnect();
			updateData(conn,sql);
			if(type == 1){					
				l = searchAllData();
			}else if(type == 2){
				l = searchAllDataOperate();
			}else if(type == 3){
				l = searchAllDataAdmin();
			}else if(type == 4){
				l = searchAllData2();
			}
			freeConnection(conn);
			return l;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}	
		return null;
	}

	public List<JiangChengDictVo> searchAllJiangchengDict() {
		try
		{
			List<JiangChengDictVo> l = new ArrayList<JiangChengDictVo>();
			String sql = "select * from DICT_JIANGCHENG order by name";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			while(rs.next())
			{
				JiangChengDictVo vo = new JiangChengDictVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.jiangcheng_type = rs.getInt("jiangcheng_type");
				switch(vo.jiangcheng_type)
				{
				case 1:vo.jiangcheng_type_str = "科室奖励";break;
				case 2:vo.jiangcheng_type_str = "科室扣款";break;
				case 3:vo.jiangcheng_type_str = "科室补发";break;
				case 4:vo.jiangcheng_type_str = "护士长奖励";break;
				case 5:vo.jiangcheng_type_str = "护士长扣款";break;
				case 6:vo.jiangcheng_type_str = "护士长补发";break;
				case 7:vo.jiangcheng_type_str = "普通人员奖励";break;
				case 8:vo.jiangcheng_type_str = "普通人员扣款";break;
				case 9:vo.jiangcheng_type_str = "普通人员补发";break;
//				<mx:Object label="科室奖励" value="1"/>
//				<mx:Object label="科室扣款" value="2"/>  
//				<mx:Object label="科室补发" value="3"/>
//				<mx:Object label="管理人员奖励" value="4"/>
//				<mx:Object label="管理人员扣款" value="5"/>  
//				<mx:Object label="管理人员补发" value="6"/>
//				<mx:Object label="普通人员奖励" value="7"/>
//				<mx:Object label="普通人员扣款" value="8"/>  
//				<mx:Object label="普通人员补发" value="9"/>   

				default:vo.jiangcheng_type_str = "";
				}
				l.add(vo);
			}
			freeConnection(conn);
			return l;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}	
		return null;
	}

	public void updateJiangchengDict(JiangChengDictVo vo) {
		try
		{
			String sql = "";
			Connection conn = getConnect();
			if(vo.id == 0)
			{
				int maxId = getNewMaxId(conn,"DICT_JIANGCHENG")+1;
				sql = "insert into DICT_JIANGCHENG " +
				" (id,name,jiangcheng_type)" +
				" values("+maxId+",'"+vo.name.trim()+"',"+vo.jiangcheng_type+")";
			}
			else
			{
				sql = "update DICT_JIANGCHENG " +
				" set name = '"+vo.name.trim()+"'," +
				" jiangcheng_type="+vo.jiangcheng_type+
				" where id = "+vo.id;
			}
			
			updateData(conn, sql);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
}
